using System;
using System.Collections.Generic;
using System.Data;

namespace ADEVSO.Data
{
    /// <summary>
    /// Abstract Class Data
    /// </summary>
    public abstract class Data
    {
        #region Variables

        protected String _Server = "";
        protected String _DataBase = "";
        protected String _ConnectionString = "";
        protected String _User = "";
        protected String _Password = "";
        protected IDbConnection _Connection;

        #endregion

        #region Constructors

        public Data()
        {
        }

        #endregion

        #region Properties

        /// <summary>
        ///	Data Server Name
        /// </summary>
        public String Server
        {
            get
            {
                return _Server;
            }
            set
            {
                _Server = value;
            }
        }

        /// <summary>
        /// Database Name
        /// </summary>
        public String DataBase
        {
            get
            {
                return _DataBase;
            }
            set
            {
                _DataBase = value;
            }
        }

        /// <summary>
        /// Creates Connection String
        /// </summary>
        public abstract String ConnectionString
        {
            get;
            set;
        }

        /// <summary>
        /// Database User Name
        /// </summary>
        protected String User
        {
            get
            {
                return _User;
            }
            set
            {
                _User = value;
            }
        }

        /// <summary>
        /// Database User Password
        /// </summary>
        protected String Password
        {
            get
            {
                return _Password;
            }
            set
            {
                _Password = value;
            }
        }

        /// <summary>
        /// Database Connection State
        /// </summary>
        public String State
        {
            get
            {
                return _Connection.State.ToString();
            }
        }

        #endregion

        #region Privates

        /// <summary>
        /// Create Database Connection
        /// </summary>
        protected IDbConnection Connection
        {
            get
            {
                if (_Connection == null || (_Connection.ConnectionString.Equals("")))
                    _Connection = CreateConnection(ConnectionString);
                //else if )
                //    _Connection = CreateConnection(ConnectionString);
                if (_Connection.State != System.Data.ConnectionState.Open)
                    _Connection.Open();
                return _Connection;
            }
        }

        #endregion

        #region Methods

        #region Readers

        /// <summary>
        /// Get a DataSet from a Store Procedure
        /// </summary>
        public DataSet GetDataSet(String StoreProcedure)
        {
            DataSet mDataSet = new DataSet();
            CreateDataAdapter(StoreProcedure).Fill(mDataSet);
            return mDataSet;
        }

        /// <summary>
        /// Get a DataSet from a Storage Procedure with Parameters
        /// </summary>
        public DataSet GetDataSet(String StoreProcedure, params Object[] Args)
        {
            DataSet mDataSet = new DataSet();
            CreateDataAdapter(StoreProcedure, Args).Fill(mDataSet);
            return mDataSet;
        }

        /// <summary>
        /// Get a DataTable from a Store Procedure
        /// </summary>
        public DataTable GetDataTable(String StoreProcedure)
        {
            DataTableCollection tables = GetDataSet(StoreProcedure).Tables;
            return tables.Count > 0 ? tables[0].Copy() : null;
        }

        /// <summary>
        /// Get a DataTable from a Store Procedure with Parameters
        /// </summary>
        public DataTable GetDataTable(String StoreProcedure, params Object[] Args)
        {
            DataTableCollection tables = GetDataSet(StoreProcedure, Args).Tables;
            return tables.Count > 0 ? tables[0].Copy() : new DataTable();
        }

        /// <summary>
        /// Get a Value from a Store Procedure
        /// </summary>
        public System.Object GetValue(String StoreProcedure)
        {
            IDbCommand Com = Command(StoreProcedure);
            Com.ExecuteNonQuery();
            Object Resp = null;
            foreach (IDbDataParameter Par in Com.Parameters)
                if (Par.Direction == ParameterDirection.InputOutput || Par.Direction == ParameterDirection.Output)
                    Resp = Par.Value;
            return Resp;
        }

        /// <summary>
        /// Get a Value from a Store Procedure with Parameters
        /// </summary>
        public Object GetValue(String StoreProcedure, params Object[] Args)
        {
            IDbCommand Com = Command(StoreProcedure);
            LoadParameters(Com, Args);
            Com.ExecuteNonQuery();
            Object Resp = null;
            foreach (IDbDataParameter Par in Com.Parameters)
                if (Par.Direction == ParameterDirection.InputOutput || Par.Direction == ParameterDirection.Output)
                    Resp = Par.Value;
            return Resp;
        }

        /// <summary>
        /// Open Database Connection
        /// </summary>
        public void Open()
        {
            _Connection = Connection;
        }

        /// <summary>
        /// Close Database Connection
        /// </summary>
        public void Close()
        {
            _Connection.Close();
        }

        /// <summary>
        /// Dispose Database Connection
        /// </summary>
        public String Dispose()
        {
            try
            {
                _Connection.Dispose();
                return "";
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }

        #endregion

        #region Actions

        public abstract List<String> GetDatabases();
        public abstract List<String> GetTables();
        public abstract List<Sql.PrimaryKey> GetTablePKs(String Table);
        public abstract List<Sql.Column> GetTableColumns(String TableName);
        public abstract List<Sql.ForeignKey> GetTableFKs(String Table);
        public abstract List<Sql.Identity> GetTableIdentities(String Table);
        protected abstract IDbConnection CreateConnection(String Cadena);
        protected abstract IDbCommand Command(String StoreProcedure);
        protected abstract IDataAdapter CreateDataAdapter(String StoreProcedure, params Object[] Args);
        protected abstract void LoadParameters(System.Data.IDbCommand Command, Object[] Args);
        public abstract int SaveImage(String TableName, String IdColumnName, String ImageColumnName, long Id, byte[] Image);        

        /// <summary>
        /// Execute Store Procedure
        /// </summary>
        public int Execute(String StoreProcedure)
        {
            return Command(StoreProcedure).ExecuteNonQuery();
        }

        /// <summary>
        /// Execute Store Procedure with Parameters
        /// </summary>
        public int Execute(String StoreProcedure, params  Object[] Args)
        {
            IDbCommand Com = Command(StoreProcedure);
            LoadParameters(Com, Args);
            int Resp = Com.ExecuteNonQuery();
            for (int i = 0; i < Com.Parameters.Count; i++)
            {
                IDbDataParameter Par = (IDbDataParameter)Com.Parameters[i];
                if (Par.Direction == ParameterDirection.InputOutput || Par.Direction == ParameterDirection.Output)
                    Args.SetValue(Par.Value, i - 1);
            }
            return Resp;
        }

        /// <summary>
        /// ExecuteScalar Store Procedure
        /// </summary>
        public byte[] ExecuteScalar(String StoreProcedure)
        {
            return (byte[]) Command(StoreProcedure).ExecuteScalar();
        }

        /// <summary>
        /// ExecuteScalar Store Procedure with Parameters
        /// </summary>
        public byte[] ExecuteScalar(String StoreProcedure, params Object[] Args)
        {
            IDbCommand Com = Command(StoreProcedure);
            LoadParameters(Com, Args);
            byte[] Resp = (byte[]) Com.ExecuteScalar();
            for (int i = 0; i < Com.Parameters.Count; i++)
            {
                IDbDataParameter Par = (IDbDataParameter)Com.Parameters[i];
                if (Par.Direction == ParameterDirection.InputOutput || Par.Direction == ParameterDirection.Output)
                    Args.SetValue(Par.Value, i - 1);
            }
            return Resp;
        }

        #endregion

        #region Transactions

        protected IDbTransaction _Transaction;
        protected bool _InTransaction = false;
        protected int _Transactions;

        /// <summary>
        /// Begin Database Transaction
        /// </summary>
        public void BeginTransaction()
        {
            if (!_InTransaction)
            {
                _Transaction = Connection.BeginTransaction();
                _InTransaction = true;
            }
            _Transactions++;
        }

        /// <summary>
        /// Commit Database Transaction
        /// </summary>
        public void CommitTransaction()
        {
            if (_InTransaction && _Transactions == 1)
            {
                try
                {
                    _Transaction.Commit();
                }
                catch (Exception Ex)
                {
                    throw Ex;
                }
                finally
                {
                    _Transaction = null;
                    _InTransaction = false;
                }
            }
            _Transactions--;
        }

        /// <summary>
        /// Abort Database Transaction
        /// </summary>
        public void AbortTransaction()
        {
            if (_InTransaction)
            {
                try
                {
                    _Transaction.Rollback();
                }
                catch (Exception Ex)
                {
                    throw Ex;
                }
                finally
                {
                    _Transaction = null;
                    _InTransaction = false;
                    _Transactions = 0;
                }
            }
        }

        #endregion

        #endregion
    }
}
